package com.internship.javawebinternship.myUtils;

import com.internship.javawebinternship.controller.UploadExcelController;
import jxl.format.Alignment;
import jxl.write.*;

import java.util.regex.*;
import java.io.File;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.regex.Pattern;

import jxl.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ListToExcel {
    private static final Logger LOGGER = LoggerFactory.getLogger(UploadExcelController.class);

    /**
     * 将list集合转成Excel文件
     *
     * @param list 对象集合
     * @return 返回文件路径
     */
    public static String createExcel(List<?> list, String date) {
        String result = "";
        if (list.size() == 0 || list == null) {
            result = "ObjectException";
        } else {
            Object o = list.get(0);
            Class<?> clazz = o.getClass();
            String className = clazz.getSimpleName();
            Field[] fields = clazz.getDeclaredFields();    //通过反射获取字段数组
            String path = "excelGenerated/";
            File folder = new File(path);
            if (!folder.exists()) {
                folder.mkdirs();
            }
            String fileName = date;
            String name = fileName + " Excel Exported.xls";
            WritableWorkbook book = null;
            File file = null;
            try {
                file = new File(folder.getAbsolutePath() + File.separator + name);
                book = Workbook.createWorkbook(file);  //创建xls文件
                WritableSheet sheet = book.createSheet(className, 0);
                WritableCellFormat cellFormat = new WritableCellFormat();
                cellFormat.setAlignment(Alignment.CENTRE);
                int i = 0;  //列
                int j = 0;  //行
                for (Field f : fields) {
                    j = 0;
                    String cellName = "";
                    switch (f.getName()) {
                        case "reportid":
                            cellName = "打卡编号";
                            break;
                        case "stuempno":
                            cellName = "学号/工号";
                            break;
                        case "name":
                            cellName = "姓名";
                            break;
                        case "date":
                            cellName = "打卡日期";
                            break;
                        case "period":
                            cellName = "打卡时间段";
                            break;
                        case "temp":
                            cellName = "体温";
                            break;
                        case "location":
                            cellName = "位置";
                            break;
                        case "isfever":
                            cellName = "是否发烧";
                            break;
                        case "iscough":
                            cellName = "是否咳嗽";
                            break;
                        default:
                            cellName = "未定义";
                            break;
                    }
                    Label label = new Label(i, j, cellName, cellFormat);   //这里把字段名称写入excel第一行中
                    sheet.addCell(label);
                    sheet.setColumnView(i, sheet.getCell(i, j).getContents().length() * 2 + 4);
                    j = 1;
                    for (Object obj : list) {
                        Object temp = getFieldValueByName(f.getName(), obj);
                        String strTemp = "";
                        if (temp != null) {
                            strTemp = temp.toString();
                        }

                        if (strTemp.equals("0")) strTemp = "否";
                        if (strTemp.equals("1")) strTemp = "是";

                        String pattern = "[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}";
                        if (Pattern.matches(pattern, strTemp)) {
                            strTemp = strTemp.split(" ")[0];
                        }

                        sheet.addCell(new Label(i, j, strTemp, cellFormat));  //把每个对象此字段的属性写入这一列excel中
//                        sheet.setColumnView(i, sheet.getCell(i, j).getContents().length() + 2);
                        j++;
                    }
                    i++;
                }
                book.write();
                result = file.getPath();
            } catch (Exception e) {
                result = "SystemException";
                e.printStackTrace();
            } finally {

                String[] Name = folder.list();
                Date DATE = new Date();

                //自动删除7天前生成的文件
                for (String i : Name) {
                    File temp = new File("excelGenerated/" + i);
                    if (DATE.getTime() - temp.lastModified() > 604800000) {
                        try {
                            File del = new File(temp.getAbsolutePath());
                            if (del.delete()) {
                                LOGGER.info("旧文件删除成功");
                            }
                        } catch (Exception e) {
                            LOGGER.info("旧文件删除失败");
                        }
                    }
                }

                fileName = null;
                name = null;
                folder = null;
                file = null;
                if (book != null) {
                    try {
                        book.close();
                    } catch (WriteException e) {
                        result = "WriteException";
                        e.printStackTrace();
                    } catch (IOException e) {
                        result = "IOException";
                        e.printStackTrace();
                    }
                }
            }
        }

        if (!result.contains("Exception")) {
            return result;   //最后输出文件路径
        } else return "";
    }

    /**
     * 获取属性值
     *
     * @param fieldName 字段名称
     * @param o         对象
     * @return Object
     */
    private static Object getFieldValueByName(String fieldName, Object o) {
        try {
            String firstLetter = fieldName.substring(0, 1).toUpperCase();
            String getter = "get" + firstLetter + fieldName.substring(1);    //获取方法名
            Method method = o.getClass().getMethod(getter);  //获取方法对象
            Object value = method.invoke(o);    //用invoke调用此对象的get字段方法
            return value;  //返回值
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}
